﻿using System;
using System.Collections.Generic;
using System.Linq;
using System.Text;
using YXShop.IDAL.SystemInfo;
using System.Data;
using System.Data.SqlClient;

namespace YXShop.SQLServerDAL.SystemInfo
{
    public class BankAccount:IBankAccount
    {
        public BankAccount()
        {
        }
        #region "Database Operation"
        /// <summary>
        /// 添加
        /// </summary>
        /// <param name="model"></param>
        /// <returns></returns>
        public int Add(YXShop.Model.SystemInfo.BankAccount model)
        {
            StringBuilder strSql = new StringBuilder();
            strSql.Append("insert into [yxs_bankaccount](");
            strSql.Append("[bank_name],[bank_bankac],[bank_account],[bank_number],[bank_diagram],[bank_icon],[bank_putout],[bank_putoutID],[bank_putouttypeid],[bank_ordernum],[bank_putoutname])");
            strSql.Append("values(");
            strSql.Append("@name,@bankac,@account,@number,@diagram,@icon,@putout,@putoutid,@putouttypeid,@ordernum,@putoutname) Select scope_IDENTITY()");
            SqlParameter[] parameters = (SqlParameter[])this.ValueParams(model);
            object obj = ChangeHope.DataBase.SQLServerHelper.GetSingle(strSql.ToString(),parameters);
            if (obj == null)
            {
                return 0;
            }
            else
            {
                return Convert.ToInt32(obj);
            }
        }
        /// <summary>
        /// 修改
        /// </summary>
        /// <param name="model"></param>
        public void Update(YXShop.Model.SystemInfo.BankAccount model)
        {
            StringBuilder strSql = new StringBuilder();
            strSql.Append("update [yxs_bankaccount] set ");
            strSql.Append("[bank_name]=@name,[bank_bankac]=@bankac,[bank_account]=@account,[bank_number]=@number,[bank_diagram]=@diagram,[bank_icon]=@icon,[bank_putout]=@putout,[bank_putoutID]=@putoutid,[bank_putouttypeid]=@putouttypeid,[bank_ordernum]=@ordernum,[bank_putoutname]=@putoutname  ");
            strSql.Append("where [bank_id]=@id ");
            SqlParameter[] parameters = (SqlParameter[])this.ValueParams(model);
            ChangeHope.DataBase.SQLServerHelper.ExecuteSql(strSql.ToString(),parameters);
        }
        /// <summary>
        /// 删除
        /// </summary>
        /// <param name="id"></param>
        public void Delete(int id)
        {
            StringBuilder strSql = new StringBuilder();
            strSql.Append("delete from [yxs_bankaccount] ");
            strSql.Append("where [bank_id]=@id");
            SqlParameter[] parameters ={
                                          new SqlParameter("@id",SqlDbType.Int,4)
                                      };
            parameters[0].Value = id;
            ChangeHope.DataBase.SQLServerHelper.ExecuteSql(strSql.ToString(),parameters);
        }
        /// <summary>
        /// 批量删除
        /// </summary>
        /// <param name="ids"></param>
        public void DeleteAll(string ids)
        {
            StringBuilder strSql = new StringBuilder();
            strSql.Append("delete from [yxs_bankaccount] where [bank_id] in(");
            strSql.Append(ids+" )");
            ChangeHope.DataBase.SQLServerHelper.ExecuteSql(strSql.ToString());
        }
        #endregion

        #region  "Data Load"
        /// <summary> 
        /// 根据ID返回对象
        /// </summary>
        /// <param name="id"></param>
        /// <returns></returns>
        public YXShop.Model.SystemInfo.BankAccount GetModelById(int id)
        {
            StringBuilder strSql = new StringBuilder();
            strSql.Append("select top 1 [bank_id],[bank_name],[bank_bankac],[bank_account],[bank_number],[bank_diagram],[bank_icon],[bank_putout],[bank_putoutID],[bank_putouttypeid],[bank_ordernum],[bank_putoutname]  from [yxs_bankaccount] ");
            strSql.Append("where [bank_id]=@id");
            SqlParameter[] parameters ={
                                          new SqlParameter("@id",SqlDbType.Int,4)
                                      };
            parameters[0].Value = id;
            YXShop.Model.SystemInfo.BankAccount model = null;
            using(SqlDataReader reader=ChangeHope.DataBase.SQLServerHelper.ExecuteReader(strSql.ToString(),parameters)){
                while(reader.Read()){
                    model = new YXShop.Model.SystemInfo.BankAccount();
                    model.Bank_id = (int)reader["bank_id"];
                    model.Bank_name = (string)reader["bank_name"];
                    model.Bank_bankac = (string)reader["bank_bankac"];
                    model.Bank_account = (string)reader["bank_account"];
                    model.Bank_number = (string)reader["bank_number"];
                    model.Bank_diagram = (string)reader["bank_diagram"];
                    model.Bank_icon = (string)reader["bank_icon"];
                    model.Bank_putout = (int)reader["bank_putout"];
                    model.Bank_putoutid = (int)reader["bank_putoutID"];
                    model.Bank_putouttypeid = (int)reader["bank_putouttypeid"];
                    model.Bank_ordernum = (int)reader["bank_ordernum"];
                    model.Bank_putoutname = (string)reader["bank_putoutname"];
                }
            }
            return model;

        }

        /// <summary>
        /// 得到银行所有集合
        /// </summary>
        /// <returns></returns>
        public List<YXShop.Model.SystemInfo.BankAccount> GetAll()
        {
            List<YXShop.Model.SystemInfo.BankAccount> list = new List<YXShop.Model.SystemInfo.BankAccount>();
            StringBuilder strSql = new StringBuilder();
            strSql.Append("select [bank_id],[bank_name],[bank_bankac],[bank_account],[bank_number],[bank_diagram],[bank_icon],[bank_putout],[bank_putoutID],[bank_putouttypeid],[bank_ordernum],[bank_putoutname] from [yxs_bankaccount] ");
            strSql.Append("where 1=1");
          
            using (SqlDataReader reader = ChangeHope.DataBase.SQLServerHelper.ExecuteReader(strSql.ToString()))
            {
                while (reader.Read())
                {
                    YXShop.Model.SystemInfo.BankAccount model = new YXShop.Model.SystemInfo.BankAccount();
                    model.Bank_id = (int)reader["bank_id"];
                    model.Bank_name = (string)reader["bank_name"];
                    model.Bank_bankac = (string)reader["bank_bankac"];
                    model.Bank_account = (string)reader["bank_account"];
                    model.Bank_number = (string)reader["bank_number"];
                    model.Bank_diagram = (string)reader["bank_diagram"];
                    model.Bank_icon = (string)reader["bank_icon"];
                    model.Bank_putout = (int)reader["bank_putout"];
                    model.Bank_putoutid = (int)reader["bank_putouttypeid"];
                    model.Bank_putouttypeid = (int)reader["bank_putouttypeid"];
                    model.Bank_ordernum = (int)reader["bank_ordernum"];
                    model.Bank_putoutname = (string)reader["bank_putoutname"];
                    list.Add(model);
                }
            }
            return list;
        }

        /// <summary>
        /// 分页
        /// </summary>
        /// <returns></returns>
        public ChangeHope.DataBase.DataByPage GetAllList()
        {
            string sql = "[select] * [from] yxs_bankaccount [where] 1=1 [order by] bank_ordernum DESC ";
            ChangeHope.DataBase.DataByPage dataPage = new ChangeHope.DataBase.DataByPage();
            dataPage.Sql = sql;
            dataPage.GetRecordSetByPage();
            return dataPage;
        }
        /// <summary>
        /// 排序所有记录
        /// </summary>
        /// <remarks></remarks>
        public ChangeHope.DataBase.DataByPage GetList(string orderfield, int pagesize, string Conditions)
        {
            ChangeHope.DataBase.DataByPage dataPage = new ChangeHope.DataBase.DataByPage();
            dataPage.Sql = "[select] * [from] yxs_bankaccount [where] 1=1 " + Conditions + " " + orderfield;
            dataPage.PageSize = pagesize;
            dataPage.GetRecordSetByPage();
            return dataPage;
        }
        #endregion

        #region "Other Connection"

        public IDataParameter[] ValueParams(YXShop.Model.SystemInfo.BankAccount model)
        {
            SqlParameter[] parameters = new SqlParameter[12];
            parameters[0] = new SqlParameter("@id",SqlDbType.Int,4);
            parameters[1] = new SqlParameter("@name",SqlDbType.VarChar,100);
            parameters[2] = new SqlParameter("@bankac",SqlDbType.VarChar,100);
            parameters[3] = new SqlParameter("@account",SqlDbType.NVarChar,50);
            parameters[4] = new SqlParameter("@number",SqlDbType.VarChar,50);
            parameters[5] = new SqlParameter("@diagram",SqlDbType.VarChar,100);
            parameters[6] = new SqlParameter("@icon",SqlDbType.VarChar,100);
            parameters[7] = new SqlParameter("@putout",SqlDbType.Int,4);
            parameters[8] = new SqlParameter("@putoutid",SqlDbType.Int,4);
            parameters[9] = new SqlParameter("@putouttypeid",SqlDbType.Int,4);
            parameters[10] = new SqlParameter("@ordernum",SqlDbType.Int,4);
            parameters[11] = new SqlParameter("@putoutname",SqlDbType.VarChar,50);
            parameters[0].Value = model.Bank_id;
            parameters[1].Value = model.Bank_name;
            parameters[2].Value = model.Bank_bankac;
            parameters[3].Value = model.Bank_account;
            parameters[4].Value = model.Bank_number;
            parameters[5].Value = model.Bank_diagram;
            parameters[6].Value = model.Bank_icon;
            parameters[7].Value = model.Bank_putout;
            parameters[8].Value = model.Bank_putoutid;
            parameters[9].Value = model.Bank_putouttypeid;
            parameters[10].Value = model.Bank_ordernum;
            parameters[11].Value = model.Bank_putoutname;
            return parameters;

        }
        #endregion
    }
}
